package ddpdecoder;

import java.sql.*;
import java.util.*;
import java.math.BigDecimal;
@SuppressWarnings("unused")
public class Database {

//	static String driver = "com.mysql.jdbc.Driver"; 			// driver name
//	static String url = "jdbc:mysql://10.71.10.71:3306/ddp"; 	// data base URL
//	static String user = "ddp"; 								// user name
//	static String password = "ddprocks"; 					    // key
	static String driver = "com.mysql.jdbc.Driver"; 			// driver name
	static String url = "jdbc:mysql://127.0.0.1:3306/test3"; 	// data base URL
	static String user = "root"; 								// user name
	static String password = "ddpserver2013"; 					// key
	Connection conn;

	public Database() {

		try {
			Class.forName(driver);								// load the data base driver
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("Data base connected.");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public void putIp(String ip){
		try {
			Statement statement = conn.createStatement();
			String query = "insert into ip values('" + ip + "', now())";
			statement.execute(query);
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void wipeIp(String ip){
		try {
			Statement statement = conn.createStatement();
			String query = "delete from ip where ip='"+ip+"'";
			statement.execute(query);
			statement.close();
			System.out.println("This IP has been wiped out from data base.");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void closeDb() throws SQLException{
		conn.close();
	}

	public void saveInt(int api, int data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveFloat(int api, float data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void saveDouble(int api, double data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void saveByte(int api, byte data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void saveLong(int api, long data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void saveShort(int api, short data, String unit, String Description) {
		try {
			int typeid=0;
			Statement statement = conn.createStatement();
			String query=new String("select * from datatype");
			query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()) typeid=rs.getInt(1);
			else{
				query="insert into datatype values(null,'"+Description+"','"+unit+"')";
				statement.execute(query);
				query="select id from datatype where description='"+Description+"' and "+"unit='"+unit+"'";
				rs = statement.executeQuery(query);
				if(rs.next()) typeid=rs.getInt(1);
			}
			query = "insert into data(deviceid,data,time,typeid) values(" + api + ","
					+ data + " , now() , " + typeid + ")";
			statement.execute(query);
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean checkAPI(int apikey) throws SQLException{
		int APIKEY;
		Statement statement = conn.createStatement();
		String query = "select * from device where id="+apikey;
		ResultSet rs = statement.executeQuery(query);
	    if(rs.next()){
	    	return true;
	    }
	    return false;
	}
	
	public void test() throws SQLException { 					// for testing
		Statement statement = conn.createStatement();
		String query = "select * from data";
		ResultSet rs = statement.executeQuery(query);
		while (rs.next()) {
			System.out.println(rs.getString(1));
			System.out.println(rs.getString(2));
			System.out.println(rs.getString(3));
			System.out.println(rs.getString(4));
			System.out.println(rs.getString(5));
		}
	}
	
    public void test2() throws SQLException{                    //test if there is mistake saving byte, short....data as double 
    	byte a=120;
    	int b=19900;
    	float c =234.113f;
    	short d=23;
    	char e='0';
    	Statement statement = conn.createStatement();
		String query = "insert into data values("+e+")";
		statement.execute(query);
    }
    
	public static void main(String[] args) throws SQLException { // for unit
																 // test
		Database db = new Database();
//		db.test();
//		db.test2();
		db.saveInt(1, 514, "km", "test");
	}
}

